package com.school.dao.impl;

import com.school.config.DbcpConfig;
import com.school.dao.RoleDao;
import com.school.model.Permission;
import com.school.model.Role;

import java.sql.*;
import java.util.*;

public class RoleDaoImpl extends BaseDaoImpl<Role> implements RoleDao {

    @Override
    public Role insert(Role role) {
        String sql = "insert into " + tableName + " (rolename,note) value (?,?)";
        try {
            // 创建一个PreparedStatement对象，用于执行一次查询过程
            PreparedStatement statement = DbcpConfig.getPreparedStatement(sql);
            if (statement != null) {
                statement.setString(1, role.getRolename());
                statement.setString(2, role.getNote());
                // 执行上面的sql语句取查询，得到查询的条数
                statement.executeUpdate();
                // 获取最后操作的id集合
                ResultSet keys = statement.getGeneratedKeys();
                // 放到student对象中
                while (keys.next()) {
                    role.setId(keys.getInt(1));
                }
                // 关闭查询过程
                statement.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        // 返回插入的条数
        return role;
    }

    @Override
    public List<Role> insert(List<Role> roles) {
        String sql = "insert into " + tableName + " (rolename,note) values (?,?)";
        try {
            // 获取连接对象
            Connection connection = DbcpConfig.getConnection();
            // 开启事务
            connection.setAutoCommit(false);
            // 创建一个PreparedStatement对象，用于执行一次查询过程
            PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            if (statement != null) {
                for (Role role : roles) {
                    statement.setString(1, role.getRolename());
                    statement.setString(2, role.getNote());
                    // 正常情况需要提交，但是现在暂存一次操作
                    statement.addBatch();
                }
                // 再一次性全部提交
                statement.executeBatch();
                // 获取最后操作的id集合
                ResultSet keys = statement.getGeneratedKeys();
                // 放到student对象中
                for (Role role : roles) {
                    keys.next();
                    if (keys != null) {
                        role.setId(keys.getInt(1));
                    }
                }
                // 关闭查询过程
                statement.close();
                connection.commit();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        // 返回插入的条数
        return roles;
    }

    @Override
    public int update(Role role) {
        // update student set stunum='fjsjklf', sex='女' wehere id=1
        StringBuffer sql = new StringBuffer("update " + tableName + " set ");
        if (role.getRolename() != null) {
            sql.append(" rolename = '" + role.getRolename() + "',");
        }
        if (role.getNote() != null) {
            sql.append(" note = '" + role.getNote() + "',");
        }
        // 获取”,“最后一次出现的位置下标
        int lastIndex = sql.lastIndexOf(",");
        // 删除最后一个”,“
        sql.delete(lastIndex, lastIndex + 1);
        sql.append(" where id = " + role.getId());
        try {
            PreparedStatement preparedStatement = DbcpConfig.getPreparedStatement(sql.toString());
            if (preparedStatement != null) {
                return preparedStatement.executeUpdate();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int[] update(List<Role> roles) {
        try {
            Connection connection = DbcpConfig.getConnection();
            connection.setAutoCommit(false);
            Statement statement = connection.createStatement();
            if (statement != null) {
                for (Role role : roles) {
                    // update student set stunum='fjsjklf', sex='女' wehere id=1
                    StringBuffer sql = new StringBuffer("update " + tableName + " set ");
                    if (role.getRolename() != null) {
                        sql.append(" rolename = '" + role.getRolename() + "',");
                    }
                    if (role.getNote() != null) {
                        sql.append(" note = '" + role.getNote() + "',");
                    }
                    // 获取”,“最后一次出现的位置下标
                    int lastIndex = sql.lastIndexOf(",");
                    // 删除最后一个”,“
                    sql.delete(lastIndex, lastIndex + 1);
                    sql.append(" where id = " + role.getId());
                    System.out.println(sql);
                    statement.addBatch(sql.toString());
                }
                // 返回成功和失败：1-成功；0-失败
                int res[] = statement.executeBatch();
                connection.commit();
                statement.close();
                return res;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return new int[0];
    }

    @Override
    public Role findOneById(Integer id) {
        String sql = "select id,rolename,note from " + tableName + " where id = ?";
        try {
            PreparedStatement preparedStatement = DbcpConfig.getPreparedStatement(sql);
            if (preparedStatement != null) {
                preparedStatement.setInt(1, id);
                ResultSet resultSet = preparedStatement.executeQuery();
                Role role = new Role();
                while (resultSet.next()) {
                    role.setId(resultSet.getInt("id"));
                    role.setRolename(resultSet.getString("rolename"));
                    role.setNote(resultSet.getString("note"));
                }
                preparedStatement.close();
                return role;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return new Role();
    }

    @Override
    public List<Role> findPage(Role role, Integer offset, Integer size) {
        // select * from student limit 0,10;
        StringBuffer sql = new StringBuffer("select id,rolename,note from ");
        sql.append(tableName).append(" where ");
        if (role.getRolename() != null) {
            sql.append(" rolename like '%" + role.getRolename() + "%' or ");
        }
        if (role.getNote() != null) {
            sql.append(" note like '%" + role.getNote() + "%' or ");
        }
        // 获取”,“最后一次出现的位置下标
        int lastIndex = sql.lastIndexOf("or");
        // 删除最后一个”,“
        sql.delete(lastIndex, lastIndex + 2);
        sql.append(" limit ?,?");
        System.out.println(sql.toString());
        List<Role> list = new ArrayList<>();
        try {
            PreparedStatement preparedStatement = DbcpConfig.getPreparedStatement(sql.toString());
            if (preparedStatement != null) {
                preparedStatement.setInt(1, offset);
                preparedStatement.setInt(2, size);
                ResultSet resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    Role role1 = new Role();
                    role1.setId(resultSet.getInt("id"));
                    role1.setRolename(resultSet.getString("rolename"));
                    role1.setNote(resultSet.getString("note"));
                    list.add(role1);
                }
                preparedStatement.close();
                return list;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    @Override
    public Set<Integer> getPermissionIdsByRoleIds(List<Integer> roleIds) {
        String sql = "select permission_id from role_permission where role_id = ?";
        try {
            Connection connection = DbcpConfig.getConnection();
            if (connection != null) {
                Set<Integer> permissionIds = new HashSet<>();
                connection.setAutoCommit(false);
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
                if (preparedStatement != null) {
                    for (Integer roleId : roleIds) {
                        preparedStatement.setInt(1, roleId);
                        ResultSet resultSet = preparedStatement.executeQuery();
                        while (resultSet.next()) {
                            permissionIds.add(resultSet.getInt("permission_id"));
                        }
                    }
                }
                connection.commit();
                preparedStatement.close();
                return permissionIds;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Permission> getPermissionsByIds(Collection<Integer> permissionIds) {
        String sql = "select `id`,`url`,`method`,`type`,`note` from permission where id = ?";
        try {
            Connection connection = DbcpConfig.getConnection();
            if (connection != null) {
                List<Permission> permissions = new LinkedList<>();
                connection.setAutoCommit(false);
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
                if (preparedStatement != null) {
                    for (Integer id : permissionIds) {
                        preparedStatement.setInt(1, id);
                        ResultSet resultSet = preparedStatement.executeQuery();
                        while (resultSet.next()) {
                            Permission permission = new Permission();
                            permission.setId(resultSet.getInt("id"));
                            permission.setUrl(resultSet.getString("url"));
                            permission.setMethod(resultSet.getString("method"));
                            permission.setType(resultSet.getString("type"));
                            permission.setNote(resultSet.getString("note"));
                            permissions.add(permission);
                        }
                    }
                }
                connection.commit();
                preparedStatement.close();
                return permissions;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int deleteRolesByUserId(Integer userId) {
        String sql = "delete from user_role where user_id = ?";
        try {
            PreparedStatement preparedStatement = DbcpConfig.getPreparedStatement(sql);
            if (preparedStatement != null) {
                preparedStatement.setInt(1, userId);
                return preparedStatement.executeUpdate();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int insertUserRolesByUserId(Integer userId, Set<Integer> roleIds) {
        String sql = "insert into user_role values(?,?)";
        Connection connection = DbcpConfig.getConnection();
        try {
            if (connection != null) {
                connection.setAutoCommit(false);
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
                if (preparedStatement != null) {
                    for (Integer roleId : roleIds) {
                        preparedStatement.setInt(1, userId);
                        preparedStatement.setInt(2, roleId);
                        preparedStatement.addBatch(); // 添加一次，缓存并不立即执行
                    }
                    int[] ints = preparedStatement.executeBatch();// 一次性执行
                    connection.commit();
                    preparedStatement.close();
                    return ints.length;
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }
}
